using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using ReportToolAPI.Models;
using ReportToolAPI.Services;

namespace ReportToolAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class BasicController : ControllerBase
    {
        private readonly DatabaseConfigService _configService;

        public BasicController(DatabaseConfigService configService)
        {
            _configService = configService;
        }

        /// <summary>
        /// 配料信息查询
        /// </summary>
        [HttpPost("ingredient-list")]
        public async Task<IActionResult> IngredientList([FromBody] IngredientRequest request)
        {
            try
            {
                // 获取数据库配置
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<List<IngredientResult>>.Fail("数据库配置不存在", 404));
                }

                // 构建连接字符串
                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);

                var results = new List<IngredientResult>();

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 构建SQL查询语句
                    var sql = @"
                        SELECT 
                            p.item_no as 货号,
                            i.item_subno as 国际条码,
                            i.item_name as 品名,
                            p.item_material as 配料表,
                            p.item_standard as 执行标准,
                            p.item_weight as 净含量,
                            p.item_condition as 存储条件,
                            p.item_way as 加工方式,
                            p.item_memo1 as 生产商,
                            p.item_memo2 as 生产产地,
                            p.item_memo3 as 属性3,
                            p.item_memo4 as 属性4,
                            p.item_memo5 as 属性5,
                            p.item_memo6 as 属性6
                        FROM t_bd_barcode_prc_property p
                        LEFT JOIN t_bd_item_info i ON p.item_no = i.item_no
                        WHERE 1=1";

                    // 如果有关键词，添加模糊查询条件（匹配货号、国际条码、品名）
                    if (!string.IsNullOrWhiteSpace(request.Keyword))
                    {
                        sql += " AND (p.item_no LIKE @Keyword OR i.item_subno LIKE @Keyword OR i.item_name LIKE @Keyword)";
                    }

                    using (var command = new SqlCommand(sql, connection))
                    {
                        // 添加参数
                        if (!string.IsNullOrWhiteSpace(request.Keyword))
                        {
                            command.Parameters.AddWithValue("@Keyword", $"%{request.Keyword}%");
                        }

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (await reader.ReadAsync())
                            {
                                results.Add(new IngredientResult
                                {
                                    ItemNo = reader["货号"]?.ToString() ?? "",
                                    Barcode = reader["国际条码"]?.ToString() ?? "",
                                    ItemName = reader["品名"]?.ToString() ?? "",
                                    Material = reader["配料表"]?.ToString() ?? "",
                                    Standard = reader["执行标准"]?.ToString() ?? "",
                                    Weight = reader["净含量"]?.ToString() ?? "",
                                    Condition = reader["存储条件"]?.ToString() ?? "",
                                    ProcessWay = reader["加工方式"]?.ToString() ?? "",
                                    Manufacturer = reader["生产商"]?.ToString() ?? "",
                                    Origin = reader["生产产地"]?.ToString() ?? "",
                                    Attr3 = reader["属性3"]?.ToString() ?? "",
                                    Attr4 = reader["属性4"]?.ToString() ?? "",
                                    Attr5 = reader["属性5"]?.ToString() ?? "",
                                    Attr6 = reader["属性6"]?.ToString() ?? ""
                                });
                            }
                        }
                    }
                }

                Console.WriteLine($"✓ 配料信息查询成功，共 {results.Count} 条数据");
                return Ok(ApiResponse<List<IngredientResult>>.Ok(results, $"查询成功，共 {results.Count} 条数据"));
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL查询失败: {ex.Message}");
                return Ok(ApiResponse<List<IngredientResult>>.Fail($"SQL查询失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询异常: {ex.Message}");
                return Ok(ApiResponse<List<IngredientResult>>.Fail($"查询异常: {ex.Message}"));
            }
        }

        /// <summary>
        /// 检查货号（新增时使用）
        /// </summary>
        [HttpPost("ingredient-check")]
        public async Task<IActionResult> IngredientCheck([FromBody] IngredientCheckRequest request)
        {
            try
            {
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<object>.Fail("数据库配置不存在", 404));
                }

                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);
                var result = new IngredientCheckResult
                {
                    ExistsInProperty = false,
                    ExistsInItem = false
                };

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 1. 先检查配料表 t_bd_barcode_prc_property（匹配货号或国际条码）
                    var sqlProperty = @"
                        SELECT 
                            p.item_no,
                            i.item_subno,
                            i.item_name,
                            p.item_material,
                            p.item_standard,
                            p.item_weight,
                            p.item_condition,
                            p.item_way,
                            p.item_memo1,
                            p.item_memo2,
                            p.item_memo3,
                            p.item_memo4,
                            p.item_memo5,
                            p.item_memo6
                        FROM t_bd_barcode_prc_property p
                        LEFT JOIN t_bd_item_info i ON p.item_no = i.item_no
                        WHERE p.item_no = @ItemNo OR i.item_subno = @ItemNo";

                    using (var command = new SqlCommand(sqlProperty, connection))
                    {
                        command.Parameters.AddWithValue("@ItemNo", request.ItemNo);

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                // 配料表中已存在
                                result.ExistsInProperty = true;
                                result.ItemNo = reader["item_no"]?.ToString() ?? "";
                                result.Barcode = reader["item_subno"]?.ToString() ?? "";
                                result.ItemName = reader["item_name"]?.ToString() ?? "";
                                result.Material = reader["item_material"]?.ToString() ?? "";
                                result.Standard = reader["item_standard"]?.ToString() ?? "";
                                result.Weight = reader["item_weight"]?.ToString() ?? "";
                                result.Condition = reader["item_condition"]?.ToString() ?? "";
                                result.ProcessWay = reader["item_way"]?.ToString() ?? "";
                                result.Manufacturer = reader["item_memo1"]?.ToString() ?? "";
                                result.Origin = reader["item_memo2"]?.ToString() ?? "";
                                result.Attr3 = reader["item_memo3"]?.ToString() ?? "";
                                result.Attr4 = reader["item_memo4"]?.ToString() ?? "";
                                result.Attr5 = reader["item_memo5"]?.ToString() ?? "";
                                result.Attr6 = reader["item_memo6"]?.ToString() ?? "";

                                Console.WriteLine($"✓ 配料表中已存在（货号或条码）：{request.ItemNo}");
                                return Ok(ApiResponse<IngredientCheckResult>.Ok(result, "配料表中已存在"));
                            }
                        }
                    }

                    // 2. 如果配料表中不存在，检查商品信息表 t_bd_item_info（匹配货号或国际条码）
                    var sqlItem = @"
                        SELECT item_no, item_subno, item_name
                        FROM t_bd_item_info
                        WHERE item_no = @ItemNo OR item_subno = @ItemNo";

                    using (var command = new SqlCommand(sqlItem, connection))
                    {
                        command.Parameters.AddWithValue("@ItemNo", request.ItemNo);

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())
                            {
                                // 商品信息表中存在
                                result.ExistsInItem = true;
                                result.ItemNo = reader["item_no"]?.ToString() ?? "";
                                result.Barcode = reader["item_subno"]?.ToString() ?? "";
                                result.ItemName = reader["item_name"]?.ToString() ?? "";

                                Console.WriteLine($"✓ 商品信息表中找到（货号或条码）：{request.ItemNo}");
                                return Ok(ApiResponse<IngredientCheckResult>.Ok(result, "商品信息存在"));
                            }
                        }
                    }
                }

                // 3. 两个表都不存在
                Console.WriteLine($"❌ 商品信息不存在：{request.ItemNo}");
                return Ok(ApiResponse<IngredientCheckResult>.Ok(result, "商品信息不存在"));
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL查询失败: {ex.Message}");
                return Ok(ApiResponse<IngredientCheckResult>.Fail($"SQL查询失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询异常: {ex.Message}");
                return Ok(ApiResponse<IngredientCheckResult>.Fail($"查询异常: {ex.Message}"));
            }
        }

        /// <summary>
        /// 新增配料信息
        /// </summary>
        [HttpPost("ingredient-add")]
        public async Task<IActionResult> IngredientAdd([FromBody] IngredientUpdateRequest request)
        {
            try
            {
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<object>.Fail("数据库配置不存在", 404));
                }

                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 插入 SQL 语句
                    var sql = @"
                        INSERT INTO t_bd_barcode_prc_property 
                        (item_no, item_material, item_standard, item_weight, item_condition, 
                         item_way, item_memo1, item_memo2, item_memo3, item_memo4, item_memo5, item_memo6)
                        VALUES 
                        (@ItemNo, @Material, @Standard, @Weight, @Condition, 
                         @ProcessWay, @Manufacturer, @Origin, @Attr3, @Attr4, @Attr5, @Attr6)";

                    using (var command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@ItemNo", request.ItemNo);
                        command.Parameters.AddWithValue("@Material", (object?)request.Material ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Standard", (object?)request.Standard ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Weight", (object?)request.Weight ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Condition", (object?)request.Condition ?? DBNull.Value);
                        command.Parameters.AddWithValue("@ProcessWay", (object?)request.ProcessWay ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Manufacturer", (object?)request.Manufacturer ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Origin", (object?)request.Origin ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr3", (object?)request.Attr3 ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr4", (object?)request.Attr4 ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr5", (object?)request.Attr5 ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr6", (object?)request.Attr6 ?? DBNull.Value);

                        var rowsAffected = await command.ExecuteNonQueryAsync();
                        
                        if (rowsAffected > 0)
                        {
                            Console.WriteLine($"✓ 配料信息新增成功，货号：{request.ItemNo}");
                            return Ok(ApiResponse<object>.Ok(null, "新增成功"));
                        }
                        else
                        {
                            return Ok(ApiResponse<object>.Fail("新增失败"));
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL插入失败: {ex.Message}");
                return Ok(ApiResponse<object>.Fail($"SQL插入失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 插入异常: {ex.Message}");
                return Ok(ApiResponse<object>.Fail($"插入异常: {ex.Message}"));
            }
        }

        /// <summary>
        /// 修改配料信息
        /// </summary>
        [HttpPost("ingredient-update")]
        public async Task<IActionResult> IngredientUpdate([FromBody] IngredientUpdateRequest request)
        {
            try
            {
                // 获取数据库配置
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<object>.Fail("数据库配置不存在", 404));
                }

                // 构建连接字符串
                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 更新 SQL 语句
                    var sql = @"
                        UPDATE t_bd_barcode_prc_property
                        SET item_material = @Material,
                            item_standard = @Standard,
                            item_weight = @Weight,
                            item_condition = @Condition,
                            item_way = @ProcessWay,
                            item_memo1 = @Manufacturer,
                            item_memo2 = @Origin,
                            item_memo3 = @Attr3,
                            item_memo4 = @Attr4,
                            item_memo5 = @Attr5,
                            item_memo6 = @Attr6
                        WHERE item_no = @ItemNo";

                    using (var command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@ItemNo", request.ItemNo);
                        command.Parameters.AddWithValue("@Material", (object?)request.Material ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Standard", (object?)request.Standard ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Weight", (object?)request.Weight ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Condition", (object?)request.Condition ?? DBNull.Value);
                        command.Parameters.AddWithValue("@ProcessWay", (object?)request.ProcessWay ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Manufacturer", (object?)request.Manufacturer ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Origin", (object?)request.Origin ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr3", (object?)request.Attr3 ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr4", (object?)request.Attr4 ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr5", (object?)request.Attr5 ?? DBNull.Value);
                        command.Parameters.AddWithValue("@Attr6", (object?)request.Attr6 ?? DBNull.Value);

                        var rowsAffected = await command.ExecuteNonQueryAsync();
                        
                        if (rowsAffected > 0)
                        {
                            Console.WriteLine($"✓ 配料信息修改成功，货号：{request.ItemNo}");
                            return Ok(ApiResponse<object>.Ok(null, "修改成功"));
                        }
                        else
                        {
                            return Ok(ApiResponse<object>.Fail("未找到对应的配料信息"));
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL更新失败: {ex.Message}");
                return Ok(ApiResponse<object>.Fail($"SQL更新失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 更新异常: {ex.Message}");
                return Ok(ApiResponse<object>.Fail($"更新异常: {ex.Message}"));
            }
        }

        /// <summary>
        /// 删除配料信息
        /// </summary>
        [HttpPost("ingredient-delete")]
        public async Task<IActionResult> IngredientDelete([FromBody] IngredientDeleteRequest request)
        {
            try
            {
                // 获取数据库配置
                var dbConfig = _configService.GetById(request.DatabaseId);
                if (dbConfig == null)
                {
                    return Ok(ApiResponse<object>.Fail("数据库配置不存在", 404));
                }

                // 构建连接字符串
                var connectionString = DatabaseTestService.BuildConnectionString(dbConfig);

                using (var connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();

                    // 删除 SQL 语句
                    var sql = "DELETE FROM t_bd_barcode_prc_property WHERE item_no = @ItemNo";

                    using (var command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@ItemNo", request.ItemNo);

                        var rowsAffected = await command.ExecuteNonQueryAsync();
                        
                        if (rowsAffected > 0)
                        {
                            Console.WriteLine($"✓ 配料信息删除成功，货号：{request.ItemNo}");
                            return Ok(ApiResponse<object>.Ok(null, "删除成功"));
                        }
                        else
                        {
                            return Ok(ApiResponse<object>.Fail("未找到对应的配料信息"));
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"❌ SQL删除失败: {ex.Message}");
                return Ok(ApiResponse<object>.Fail($"SQL删除失败: {ex.Message}"));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 删除异常: {ex.Message}");
                return Ok(ApiResponse<object>.Fail($"删除异常: {ex.Message}"));
            }
        }
    }
}

